SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE set_video(
  dir_alias VARCHAR2, custid NUMBER) IS
  filename VARCHAR2(40);  
  file_ptr BFILE;
  CURSOR cust_csr IS
    SELECT cust_first_name FROM customers 
	    WHERE customer_id = custid FOR UPDATE;
BEGIN
  FOR rec IN cust_csr LOOP
    filename := rec.cust_first_name || '.gif';
    file_ptr := BFILENAME(dir_alias, filename);
    DBMS_LOB.FILEOPEN(file_ptr);
    UPDATE customers SET video = file_ptr
      WHERE CURRENT OF cust_csr;
    DBMS_OUTPUT.PUT_LINE('FILE: ' || filename ||
     ' SIZE: ' || DBMS_LOB.GETLENGTH(file_ptr));
    DBMS_LOB.FILECLOSE(file_ptr);
  END LOOP;
END set_video;
/

EXECUTE set_video('DATA_FILES', 844)
